{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "iu6P8Pks-Y72"
   },
   "source": [
    "[![Kaggle](https://kaggle.com/static/images/open-in-kaggle.svg)](https://kaggle.com/kernels/welcome?src=https://github.com/pixeltable/pixeltable/blob/master/docs/release/fundamentals/tables-and-data-operations.ipynb)&nbsp;&nbsp;\n",
    "[![Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/pixeltable/pixeltable/blob/master/docs/release/fundamentals/tables-and-data-operations.ipynb)&nbsp;&nbsp;\n",
    "<a href=\"https://raw.githubusercontent.com/pixeltable/pixeltable/master/docs/release/fundamentals/tables-and-data-operations.ipynb\" download><img src=\"https://img.shields.io/badge/%E2%AC%87-Download%20Notebook-blue\" alt=\"Download Notebook\"></a>\n",
    "\n",
    "# Pixeltable Fundamentals\n",
    "\n",
    "## Section 1: Tables and Data Operations\n",
    "\n",
    "Welcome to Section 1 of the [Pixeltable Fundamentals](...) tutorial, __Tables and Data Operations__. In this section, we'll learn how to:\n",
    "\n",
    "- Create and manage tables: Understand Pixeltable's table structure, create and modify tables, and work with table schemas\n",
    "- Manipulate data: Insert, update, and delete data within tables, and retrieve data from tables into Python variables\n",
    "- Filter and select data: Use `where()`, `select()`, and `order_by()` to query for specific rows and columns\n",
    "- Import data from CSV files and other file types\n",
    "\n",
    "First, let's ensure the Pixeltable library is installed in your environment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "GwjjornhycHU"
   },
   "outputs": [],
   "source": [
    "%pip install -q pixeltable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tables\n",
    "\n",
    "All data in Pixeltable is stored in tables. At a high level, a Pixeltable table behaves similarly to an ordinary SQL database table, but with many additional capabilities to support complex AI workflows. We'll introduce those advanced capabilities gradually throughout this tutorial; in this section, the focus is on basic table and data operations.\n",
    "\n",
    "Tables in Pixeltable are grouped into __directories__, which are simply user-defined namespaces. The following command creates a new directory, `fundamentals`, which we'll use to store the tables in our tutorial."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "EIBhrFB30cbF",
    "outputId": "9c30c33d-e16b-4aeb-cfc5-9a5770416d59"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to Pixeltable database at: postgresql://postgres:@/pixeltable?host=/Users/asiegel/.pixeltable/pgdata\n",
      "Created directory `fundamentals`.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<pixeltable.catalog.dir.Dir at 0x3406baeb0>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pixeltable as pxt\n",
    "\n",
    "# First we delete the `fundamentals` directory and all its contents (if\n",
    "# it exists), in order to ensure a clean environment for the tutorial.\n",
    "pxt.drop_dir('fundamentals', force=True)\n",
    "\n",
    "# Now we create the directory.\n",
    "pxt.create_dir('fundamentals')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7StQcO-i-7NF"
   },
   "source": [
    "Now let's create our first table. To create a table, we must give it a name and a __schema__ that describes the table structure. Note that prefacing the name with `fundamentals` causes it to be placed in our newly-created directory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Created table `films`.\n"
     ]
    }
   ],
   "source": [
    "films_t = pxt.create_table('fundamentals.films', {\n",
    "    'film_name': pxt.StringType(),\n",
    "    'year': pxt.IntType(),\n",
    "    'revenue': pxt.FloatType()\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To insert data into a table, we use the `insert()` method, passing it a list of Python dicts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inserting rows into `films`: 3 rows [00:00, 984.27 rows/s]\n",
      "Inserted 3 rows with 0 errors.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "UpdateStatus(num_rows=3, num_computed_values=3, num_excs=0, updated_cols=[], cols_with_excs=[])"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "films_t.insert([\n",
    "    {'film_name': 'Jurassic Park', 'year': 1993, 'revenue': 1037.5},\n",
    "    {'film_name': 'Titanic', 'year': 1997, 'revenue': 2257.8},\n",
    "    {'film_name': 'Avengers: Endgame', 'year': 2019, 'revenue': 2797.5}\n",
    "])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you're inserting just a single row, you can use an alternate syntax that is sometimes more convenient."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inserting rows into `films`: 1 rows [00:00, 1303.79 rows/s]\n",
      "Inserted 1 row with 0 errors.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "UpdateStatus(num_rows=1, num_computed_values=1, num_excs=0, updated_cols=[], cols_with_excs=[])"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "films_t.insert(film_name='Inside Out 2', year=2024, revenue=1462.7)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can peek at the data in our table with the `collect()` method, which retrieves all the rows in the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>film_name</th>\n",
       "      <th>year</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Jurassic Park</td>\n",
       "      <td>1993</td>\n",
       "      <td>1037.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Titanic</td>\n",
       "      <td>1997</td>\n",
       "      <td>2257.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>2019</td>\n",
       "      <td>2797.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Inside Out 2</td>\n",
       "      <td>2024</td>\n",
       "      <td>1462.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "           film_name  year  revenue\n",
       "0      Jurassic Park  1993   1037.5\n",
       "1            Titanic  1997   2257.8\n",
       "2  Avengers: Endgame  2019   2797.5\n",
       "3       Inside Out 2  2024   1462.7"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "films_t.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pixeltable also provides `update()` and `delete()` methods for modifying and removing data from a table; we'll see examples of them shortly."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering and Selecting Data\n",
    "\n",
    "Often you want to select only certain rows and/or certain columns in a table. You can do this with the `where()` and `select()` methods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>film_name</th>\n",
       "      <th>year</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Titanic</td>\n",
       "      <td>1997</td>\n",
       "      <td>2257.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>2019</td>\n",
       "      <td>2797.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "           film_name  year  revenue\n",
       "0            Titanic  1997   2257.8\n",
       "1  Avengers: Endgame  2019   2797.5"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "films_t.where(films_t.revenue >= 2000.0).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>film_name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Jurassic Park</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Titanic</td>\n",
       "      <td>1997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Inside Out 2</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "           film_name  year\n",
       "0      Jurassic Park  1993\n",
       "1            Titanic  1997\n",
       "2  Avengers: Endgame  2019\n",
       "3       Inside Out 2  2024"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "films_t.select(films_t.film_name, films_t.year).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note the expressions that appear inside the calls to `where()` and `select()`, such as `films_t.year`. These are __column references__ that point to specific columns within a table. In place of `films_t.year`, you can also use dictionary syntax and type `films_t['year']`, which means exactly the same thing but is sometimes more convenient."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>film_name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Jurassic Park</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Titanic</td>\n",
       "      <td>1997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Inside Out 2</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "           film_name  year\n",
       "0      Jurassic Park  1993\n",
       "1            Titanic  1997\n",
       "2  Avengers: Endgame  2019\n",
       "3       Inside Out 2  2024"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "films_t.select(films_t['film_name'], films_t['year']).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition to selecting columns directly, you can use column references inside various kinds of expressions. For example, our `revenue` numbers are given in millions of dollars. Let's say we wanted to select revenue in thousands of dollars instead; we could do that as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>film_name</th>\n",
       "      <th>col_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Jurassic Park</td>\n",
       "      <td>1037500.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Titanic</td>\n",
       "      <td>2257800.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>2797500.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Inside Out 2</td>\n",
       "      <td>1462700.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "           film_name      col_1\n",
       "0      Jurassic Park  1037500.0\n",
       "1            Titanic  2257800.0\n",
       "2  Avengers: Endgame  2797500.0\n",
       "3       Inside Out 2  1462700.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "films_t.select(films_t.film_name, films_t.revenue * 1000).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that since we selected an abstract expression rather than a specific column, Pixeltable gave it the generic name `col_1`. You can assign it a more informative name with Python keyword syntax:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>film_name</th>\n",
       "      <th>revenue_thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Jurassic Park</td>\n",
       "      <td>1037500.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Titanic</td>\n",
       "      <td>2257800.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>2797500.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Inside Out 2</td>\n",
       "      <td>1462700.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "           film_name  revenue_thousands\n",
       "0      Jurassic Park          1037500.0\n",
       "1            Titanic          2257800.0\n",
       "2  Avengers: Endgame          2797500.0\n",
       "3       Inside Out 2          1462700.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "films_t.select(films_t.film_name, revenue_thousands=films_t.revenue * 1000).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tables are Persistent"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a good time to mention a few key differences between Pixeltable tables and other familiar datastructures, such as Python dicts or Pandas dataframes.\n",
    "\n",
    "First, **Pixeltable is persistent. Unlike in-memory Python libraries such as Pandas, Pixeltable is a database**. When you reset a notebook kernel or start a new Python session, you'll have access to all the data you've stored previously in Pixeltable. Let's demonstrate this by using the IPython `%reset -f` command to clear out all our notebook variables, so that `films_t` is no longer defined."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'films_t' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[11], line 2\u001b[0m\n\u001b[1;32m      1\u001b[0m get_ipython()\u001b[38;5;241m.\u001b[39mrun_line_magic(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mreset\u001b[39m\u001b[38;5;124m'\u001b[39m, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m-f\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m----> 2\u001b[0m \u001b[43mfilms_t\u001b[49m\u001b[38;5;241m.\u001b[39mcollect()  \u001b[38;5;66;03m# Throws an exception now\u001b[39;00m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'films_t' is not defined"
     ]
    }
   ],
   "source": [
    "%reset -f\n",
    "films_t.collect()  # Throws an exception now"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `films_t` variable (along with all other variables in our Python session) has been cleared out - but that's ok, because it wasn't the source of record for our data. The `films_t` variable is just a reference to the underlying database table. We can recover it with the `get_table` command, referencing the `films` table by name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>film_name</th>\n",
       "      <th>year</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Jurassic Park</td>\n",
       "      <td>1993</td>\n",
       "      <td>1037.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Titanic</td>\n",
       "      <td>1997</td>\n",
       "      <td>2257.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>2019</td>\n",
       "      <td>2797.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Inside Out 2</td>\n",
       "      <td>2024</td>\n",
       "      <td>1462.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "           film_name  year  revenue\n",
       "0      Jurassic Park  1993   1037.5\n",
       "1            Titanic  1997   2257.8\n",
       "2  Avengers: Endgame  2019   2797.5\n",
       "3       Inside Out 2  2024   1462.7"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pixeltable as pxt\n",
    "\n",
    "films_t = pxt.get_table('fundamentals.films')\n",
    "films_t.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can always get a list of all existing tables with the Pixeltable `list_tables` command."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['test',\n",
       " 'ls_demo.videos',\n",
       " 'ls_demo.videos_2024_04_22',\n",
       " 'ls_demo.frames_2024_04_22',\n",
       " 'fundamentals.films']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pxt.list_tables()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "Note that if you're running Pixeltable on colab or kaggle, the database will persist only for as long as your colab/kaggle session remains active. If you're running it locally or on your own server, then your database will persist indefinitely (until you actively delete it).\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tables are Typed\n",
    "\n",
    "The second major difference is that **Pixeltable is strongly typed**. Because Pixeltable is a database, every column has a data type: that's why we specified `StringType`, `IntType`, and `FloatType` for the three columns when we created the table. These __type specifiers__ are _mandatory_ when creating tables, and they become part of the table schema. You can always see the table schema with the `describe()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_6d167 th {\n",
       "  text-align: center;\n",
       "}\n",
       "#T_6d167_row0_col0, #T_6d167_row0_col1, #T_6d167_row0_col2, #T_6d167_row1_col0, #T_6d167_row1_col1, #T_6d167_row1_col2, #T_6d167_row2_col0, #T_6d167_row2_col1, #T_6d167_row2_col2 {\n",
       "  white-space: pre-wrap;\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_6d167\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th id=\"T_6d167_level0_col0\" class=\"col_heading level0 col0\" >Column Name</th>\n",
       "      <th id=\"T_6d167_level0_col1\" class=\"col_heading level0 col1\" >Type</th>\n",
       "      <th id=\"T_6d167_level0_col2\" class=\"col_heading level0 col2\" >Computed With</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_6d167_row0_col0\" class=\"data row0 col0\" >film_name</td>\n",
       "      <td id=\"T_6d167_row0_col1\" class=\"data row0 col1\" >string</td>\n",
       "      <td id=\"T_6d167_row0_col2\" class=\"data row0 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_6d167_row1_col0\" class=\"data row1 col0\" >year</td>\n",
       "      <td id=\"T_6d167_row1_col1\" class=\"data row1 col1\" >int</td>\n",
       "      <td id=\"T_6d167_row1_col2\" class=\"data row1 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_6d167_row2_col0\" class=\"data row2 col0\" >revenue</td>\n",
       "      <td id=\"T_6d167_row2_col1\" class=\"data row2 col1\" >float</td>\n",
       "      <td id=\"T_6d167_row2_col2\" class=\"data row2 col2\" ></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x3407183a0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "films_t.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In a notebook, you can also just type `films_t` to see the schema; its output is identical to `films_t.describe()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_79aaa th {\n",
       "  text-align: center;\n",
       "}\n",
       "#T_79aaa_row0_col0, #T_79aaa_row0_col1, #T_79aaa_row0_col2, #T_79aaa_row1_col0, #T_79aaa_row1_col1, #T_79aaa_row1_col2, #T_79aaa_row2_col0, #T_79aaa_row2_col1, #T_79aaa_row2_col2 {\n",
       "  white-space: pre-wrap;\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_79aaa\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th id=\"T_79aaa_level0_col0\" class=\"col_heading level0 col0\" >Column Name</th>\n",
       "      <th id=\"T_79aaa_level0_col1\" class=\"col_heading level0 col1\" >Type</th>\n",
       "      <th id=\"T_79aaa_level0_col2\" class=\"col_heading level0 col2\" >Computed With</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_79aaa_row0_col0\" class=\"data row0 col0\" >film_name</td>\n",
       "      <td id=\"T_79aaa_row0_col1\" class=\"data row0 col1\" >string</td>\n",
       "      <td id=\"T_79aaa_row0_col2\" class=\"data row0 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_79aaa_row1_col0\" class=\"data row1 col0\" >year</td>\n",
       "      <td id=\"T_79aaa_row1_col1\" class=\"data row1 col1\" >int</td>\n",
       "      <td id=\"T_79aaa_row1_col2\" class=\"data row1 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_79aaa_row2_col0\" class=\"data row2 col0\" >revenue</td>\n",
       "      <td id=\"T_79aaa_row2_col1\" class=\"data row2 col1\" >float</td>\n",
       "      <td id=\"T_79aaa_row2_col2\" class=\"data row2 col2\" ></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "table 'films'\n",
       "\n",
       "Column Name   Type Computed With\n",
       "  film_name string              \n",
       "       year    int              \n",
       "    revenue  float              "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "films_t"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "In addition to <code>StringType</code>, <code>IntType</code>, and <code>FloatType</code>, Pixeltable provides several additional data types:\n",
    "<ul><li><code>BoolType</code>, whose values are <code>True</code> or <code>False</code>;</li>\n",
    "    <li><code>ArrayType</code> for numerical arrays;</li>\n",
    "    <li><code>JsonType</code>, for lists or dicts that correspond to valid JSON structures; and</li>\n",
    "    <li>The <b>media types</b> <code>ImageType</code>, <code>VideoType</code>, <code>AudioType</code>, and <code>DocumentType</code>.</li>\n",
    "</ul>\n",
    "We'll see examples of each of these types later in the tutorial.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Besides the column names and types, there's a third element to the schema, `Computed With`. We'll explain what this means in the next section of the tutorial, [Computed Columns](...)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All of the methods we've discussed so far, such as `insert()` and `get_table()`, are documented in the [Pixeltable API](https://pixeltable.github.io/pixeltable/) Documentation. The following pages are particularly relevant to this section of the tutorial:\n",
    "- [pixeltable](https://pixeltable.github.io/pixeltable/api/pixeltable/) package reference\n",
    "- [pxt.Table](https://pixeltable.github.io/pixeltable/api/table/) class reference\n",
    "- [API Cheat Sheet](https://pixeltable.github.io/pixeltable/api-cheat-sheet/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A Real-World Example: Earthquake Data\n",
    "\n",
    "Now let's dive a little deeper into Pixeltable's data operations. To showcase all the features, it'll be helpful to have a real-world dataset, rather than our toy dataset with four movies. The dataset we'll be using consists of Earthquake data drawn from the US Geological Survey: all recorded Earthquakes that occurred within 100 km of Seattle, Washington, between January 1, 2023 and June 30, 2024.\n",
    "\n",
    "The dataset is in CSV format, and we can load it into Pixeltable with the handy `import_csv()` function, which creates a new Pixeltable table from the contents of a CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Created table `earthquakes`.\n",
      "Inserting rows into `earthquakes`: 1823 rows [00:00, 13122.56 rows/s]\n",
      "Inserted 1823 rows with 0 errors.\n"
     ]
    }
   ],
   "source": [
    "eq_t = pxt.io.import_csv(\n",
    "    'fundamentals.earthquakes',  # Name for the new table\n",
    "    'https://raw.githubusercontent.com/pixeltable/pixeltable/master/docs/source/data/earthquakes.csv',\n",
    "    primary_key='id',  # Column 'id' is the primary key\n",
    "    parse_dates=[3]  # Interpret column 3 as a timestamp\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "In Pixeltable, you can always import external data by giving a URL instead of a local file path. This applies to CSV datasets, media files (such images and video), and other types of content. The URL will often be an <code>http://</code> URL, but it can also be an <code>s3://</code> URL referencing an S3 bucket.\n",
    "</div>\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "In addition to <code>import_csv</code>, Pixeltable provides a variety of other importers for structured data, such as <code>import_pandas</code> (which creates a table from a Pandas dataframe) and <code>import_excel</code>. These importers are described in the <a href=\"https://pixeltable.github.io/pixeltable/api/io/\">pixeltable.io package reference</a>.\n",
    "</div>\n",
    "\n",
    "Let's have a peek at our new dataset. The dataset contains 1823 rows, and we probably don't want to display them all at once. We can limit our query to fewer rows with the `limit()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>id</th>\n",
       "      <th>magnitude</th>\n",
       "      <th>location</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1.15</td>\n",
       "      <td>10 km NW of Belfair, Washington</td>\n",
       "      <td>2023-01-01 08:10:37.050</td>\n",
       "      <td>-122.93</td>\n",
       "      <td>47.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.29</td>\n",
       "      <td>23 km ENE of Ashford, Washington</td>\n",
       "      <td>2023-01-02 01:02:43.950</td>\n",
       "      <td>-121.76</td>\n",
       "      <td>46.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.2</td>\n",
       "      <td>23 km ENE of Ashford, Washington</td>\n",
       "      <td>2023-01-02 12:05:01.420</td>\n",
       "      <td>-121.75</td>\n",
       "      <td>46.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.52</td>\n",
       "      <td>15 km NNE of Ashford, Washington</td>\n",
       "      <td>2023-01-02 12:45:14.220</td>\n",
       "      <td>-121.95</td>\n",
       "      <td>46.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1.56</td>\n",
       "      <td>0 km WSW of Esperance, Washington</td>\n",
       "      <td>2023-01-02 13:19:27.200</td>\n",
       "      <td>-122.36</td>\n",
       "      <td>47.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   id  magnitude                           location               timestamp  \\\n",
       "0   0       1.15    10 km NW of Belfair, Washington 2023-01-01 08:10:37.050   \n",
       "1   1       0.29   23 km ENE of Ashford, Washington 2023-01-02 01:02:43.950   \n",
       "2   2       0.20   23 km ENE of Ashford, Washington 2023-01-02 12:05:01.420   \n",
       "3   3       0.52   15 km NNE of Ashford, Washington 2023-01-02 12:45:14.220   \n",
       "4   4       1.56  0 km WSW of Esperance, Washington 2023-01-02 13:19:27.200   \n",
       "\n",
       "   longitude  latitude  \n",
       "0    -122.93     47.51  \n",
       "1    -121.76     46.85  \n",
       "2    -121.75     46.86  \n",
       "3    -121.95     46.89  \n",
       "4    -122.36     47.79  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.limit(5).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A different way of achieving something similar is to use the `head()` and `tail()` methods. Pixeltable keeps track of the insertion order of all its data, and `head()` and `tail()` will always return the _earliest inserted_ and _most recently inserted_ rows in a table, respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>id</th>\n",
       "      <th>magnitude</th>\n",
       "      <th>location</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1.15</td>\n",
       "      <td>10 km NW of Belfair, Washington</td>\n",
       "      <td>2023-01-01 08:10:37.050</td>\n",
       "      <td>-122.93</td>\n",
       "      <td>47.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.29</td>\n",
       "      <td>23 km ENE of Ashford, Washington</td>\n",
       "      <td>2023-01-02 01:02:43.950</td>\n",
       "      <td>-121.76</td>\n",
       "      <td>46.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.2</td>\n",
       "      <td>23 km ENE of Ashford, Washington</td>\n",
       "      <td>2023-01-02 12:05:01.420</td>\n",
       "      <td>-121.75</td>\n",
       "      <td>46.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.52</td>\n",
       "      <td>15 km NNE of Ashford, Washington</td>\n",
       "      <td>2023-01-02 12:45:14.220</td>\n",
       "      <td>-121.95</td>\n",
       "      <td>46.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1.56</td>\n",
       "      <td>0 km WSW of Esperance, Washington</td>\n",
       "      <td>2023-01-02 13:19:27.200</td>\n",
       "      <td>-122.36</td>\n",
       "      <td>47.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   id  magnitude                           location               timestamp  \\\n",
       "0   0       1.15    10 km NW of Belfair, Washington 2023-01-01 08:10:37.050   \n",
       "1   1       0.29   23 km ENE of Ashford, Washington 2023-01-02 01:02:43.950   \n",
       "2   2       0.20   23 km ENE of Ashford, Washington 2023-01-02 12:05:01.420   \n",
       "3   3       0.52   15 km NNE of Ashford, Washington 2023-01-02 12:45:14.220   \n",
       "4   4       1.56  0 km WSW of Esperance, Washington 2023-01-02 13:19:27.200   \n",
       "\n",
       "   longitude  latitude  \n",
       "0    -122.93     47.51  \n",
       "1    -121.76     46.85  \n",
       "2    -121.75     46.86  \n",
       "3    -121.95     46.89  \n",
       "4    -122.36     47.79  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>id</th>\n",
       "      <th>magnitude</th>\n",
       "      <th>location</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1818</td>\n",
       "      <td>1.7</td>\n",
       "      <td>14 km W of Skokomish, Washington</td>\n",
       "      <td>2024-06-29 08:55:50.030</td>\n",
       "      <td>-123.35</td>\n",
       "      <td>47.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1819</td>\n",
       "      <td>1.06</td>\n",
       "      <td>7 km E of Lake McMurray, Washington</td>\n",
       "      <td>2024-06-29 12:15:19.130</td>\n",
       "      <td>-122.13</td>\n",
       "      <td>48.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1820</td>\n",
       "      <td>0.48</td>\n",
       "      <td>4 km E of Duvall, Washington</td>\n",
       "      <td>2024-06-30 09:15:43.020</td>\n",
       "      <td>-121.93</td>\n",
       "      <td>47.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1821</td>\n",
       "      <td>0.46</td>\n",
       "      <td>12 km NE of Ashford, Washington</td>\n",
       "      <td>2024-06-30 10:05:15.410</td>\n",
       "      <td>-121.93</td>\n",
       "      <td>46.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1822</td>\n",
       "      <td>0.72</td>\n",
       "      <td>6 km ENE of Oso, Washington</td>\n",
       "      <td>2024-06-30 11:12:41.900</td>\n",
       "      <td>-121.84</td>\n",
       "      <td>48.28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "     id  magnitude                             location  \\\n",
       "0  1818       1.70     14 km W of Skokomish, Washington   \n",
       "1  1819       1.06  7 km E of Lake McMurray, Washington   \n",
       "2  1820       0.48         4 km E of Duvall, Washington   \n",
       "3  1821       0.46      12 km NE of Ashford, Washington   \n",
       "4  1822       0.72          6 km ENE of Oso, Washington   \n",
       "\n",
       "                timestamp  longitude  latitude  \n",
       "0 2024-06-29 08:55:50.030    -123.35     47.32  \n",
       "1 2024-06-29 12:15:19.130    -122.13     48.31  \n",
       "2 2024-06-30 09:15:43.020    -121.93     47.75  \n",
       "3 2024-06-30 10:05:15.410    -121.93     46.84  \n",
       "4 2024-06-30 11:12:41.900    -121.84     48.28  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.tail(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<code>head(n)</code> and <code>limit(n).collect()</code> appear similar in this example. But <code>head()</code> always returns the <i>earliest</i> rows in a table, whereas <code>limit()</code> makes no promises about the ordering of its results (unless you specify an <code>order_by()</code> clause - more on this below).\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's also peek at the schema:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_d2add th {\n",
       "  text-align: center;\n",
       "}\n",
       "#T_d2add_row0_col0, #T_d2add_row0_col1, #T_d2add_row0_col2, #T_d2add_row1_col0, #T_d2add_row1_col1, #T_d2add_row1_col2, #T_d2add_row2_col0, #T_d2add_row2_col1, #T_d2add_row2_col2, #T_d2add_row3_col0, #T_d2add_row3_col1, #T_d2add_row3_col2, #T_d2add_row4_col0, #T_d2add_row4_col1, #T_d2add_row4_col2, #T_d2add_row5_col0, #T_d2add_row5_col1, #T_d2add_row5_col2 {\n",
       "  white-space: pre-wrap;\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_d2add\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th id=\"T_d2add_level0_col0\" class=\"col_heading level0 col0\" >Column Name</th>\n",
       "      <th id=\"T_d2add_level0_col1\" class=\"col_heading level0 col1\" >Type</th>\n",
       "      <th id=\"T_d2add_level0_col2\" class=\"col_heading level0 col2\" >Computed With</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_d2add_row0_col0\" class=\"data row0 col0\" >id</td>\n",
       "      <td id=\"T_d2add_row0_col1\" class=\"data row0 col1\" >int</td>\n",
       "      <td id=\"T_d2add_row0_col2\" class=\"data row0 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_d2add_row1_col0\" class=\"data row1 col0\" >magnitude</td>\n",
       "      <td id=\"T_d2add_row1_col1\" class=\"data row1 col1\" >float</td>\n",
       "      <td id=\"T_d2add_row1_col2\" class=\"data row1 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_d2add_row2_col0\" class=\"data row2 col0\" >location</td>\n",
       "      <td id=\"T_d2add_row2_col1\" class=\"data row2 col1\" >string</td>\n",
       "      <td id=\"T_d2add_row2_col2\" class=\"data row2 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_d2add_row3_col0\" class=\"data row3 col0\" >timestamp</td>\n",
       "      <td id=\"T_d2add_row3_col1\" class=\"data row3 col1\" >timestamp</td>\n",
       "      <td id=\"T_d2add_row3_col2\" class=\"data row3 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_d2add_row4_col0\" class=\"data row4 col0\" >longitude</td>\n",
       "      <td id=\"T_d2add_row4_col1\" class=\"data row4 col1\" >float</td>\n",
       "      <td id=\"T_d2add_row4_col2\" class=\"data row4 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_d2add_row5_col0\" class=\"data row5 col0\" >latitude</td>\n",
       "      <td id=\"T_d2add_row5_col1\" class=\"data row5 col1\" >float</td>\n",
       "      <td id=\"T_d2add_row5_col2\" class=\"data row5 col2\" ></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x16f900910>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "eq_t.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that while specifying a schema is mandatory when _creating_ a table, it's not always required when _importing_ data. This is because Pixeltable uses the structure of the imported data to infer the column types, when feasible. You can always override the inferred column types with the `schema_overrides` parameter of `import_csv()`.\n",
    "\n",
    "The following examples showcase some common data operations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1823"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.count()  # Number of rows in the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>id</th>\n",
       "      <th>magnitude</th>\n",
       "      <th>location</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1002</td>\n",
       "      <td>4.3</td>\n",
       "      <td>Port Townsend, WA</td>\n",
       "      <td>2023-10-09 02:21:08.960</td>\n",
       "      <td>-122.73</td>\n",
       "      <td>48.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1226</td>\n",
       "      <td>4.04</td>\n",
       "      <td>6 km W of Quilcene, Washington</td>\n",
       "      <td>2023-12-24 15:14:04.220</td>\n",
       "      <td>-122.96</td>\n",
       "      <td>47.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>699</td>\n",
       "      <td>3.91</td>\n",
       "      <td>9 km NNE of Snoqualmie, Washington</td>\n",
       "      <td>2023-08-08 10:17:23.910</td>\n",
       "      <td>-121.77</td>\n",
       "      <td>47.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1281</td>\n",
       "      <td>3.48</td>\n",
       "      <td>7 km SSW of River Road, Washington</td>\n",
       "      <td>2024-01-15 07:25:05.920</td>\n",
       "      <td>-123.17</td>\n",
       "      <td>48.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1355</td>\n",
       "      <td>3.42</td>\n",
       "      <td>17 km WSW of Brinnon, Washington</td>\n",
       "      <td>2024-02-16 16:30:18.830</td>\n",
       "      <td>-123.09</td>\n",
       "      <td>47.59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "     id  magnitude                            location  \\\n",
       "0  1002       4.30                   Port Townsend, WA   \n",
       "1  1226       4.04      6 km W of Quilcene, Washington   \n",
       "2   699       3.91  9 km NNE of Snoqualmie, Washington   \n",
       "3  1281       3.48  7 km SSW of River Road, Washington   \n",
       "4  1355       3.42    17 km WSW of Brinnon, Washington   \n",
       "\n",
       "                timestamp  longitude  latitude  \n",
       "0 2023-10-09 02:21:08.960    -122.73     48.04  \n",
       "1 2023-12-24 15:14:04.220    -122.96     47.82  \n",
       "2 2023-08-08 10:17:23.910    -121.77     47.60  \n",
       "3 2024-01-15 07:25:05.920    -123.17     48.00  \n",
       "4 2024-02-16 16:30:18.830    -123.09     47.59  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 5 highest-magnitude earthquakes\n",
    "\n",
    "eq_t.order_by(eq_t.magnitude, asc=False).limit(5).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>id</th>\n",
       "      <th>magnitude</th>\n",
       "      <th>location</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>699</td>\n",
       "      <td>3.91</td>\n",
       "      <td>9 km NNE of Snoqualmie, Washington</td>\n",
       "      <td>2023-08-08 10:17:23.910</td>\n",
       "      <td>-121.77</td>\n",
       "      <td>47.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>799</td>\n",
       "      <td>2.86</td>\n",
       "      <td>5 km E of Ashford, Washington</td>\n",
       "      <td>2023-08-27 10:10:23.770</td>\n",
       "      <td>-121.96</td>\n",
       "      <td>46.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>710</td>\n",
       "      <td>2.84</td>\n",
       "      <td>8 km ENE of Fall City, Washington</td>\n",
       "      <td>2023-08-08 11:51:12.750</td>\n",
       "      <td>-121.79</td>\n",
       "      <td>47.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>577</td>\n",
       "      <td>2.79</td>\n",
       "      <td>0 km NE of Maple Valley, Washington</td>\n",
       "      <td>2023-07-04 15:52:54.430</td>\n",
       "      <td>-122.04</td>\n",
       "      <td>47.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>769</td>\n",
       "      <td>2.73</td>\n",
       "      <td>16 km NE of Ashford, Washington</td>\n",
       "      <td>2023-08-22 23:44:12.250</td>\n",
       "      <td>-121.88</td>\n",
       "      <td>46.87</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "    id  magnitude                             location  \\\n",
       "0  699       3.91   9 km NNE of Snoqualmie, Washington   \n",
       "1  799       2.86        5 km E of Ashford, Washington   \n",
       "2  710       2.84    8 km ENE of Fall City, Washington   \n",
       "3  577       2.79  0 km NE of Maple Valley, Washington   \n",
       "4  769       2.73      16 km NE of Ashford, Washington   \n",
       "\n",
       "                timestamp  longitude  latitude  \n",
       "0 2023-08-08 10:17:23.910    -121.77     47.60  \n",
       "1 2023-08-27 10:10:23.770    -121.96     46.77  \n",
       "2 2023-08-08 11:51:12.750    -121.79     47.60  \n",
       "3 2023-07-04 15:52:54.430    -122.04     47.40  \n",
       "4 2023-08-22 23:44:12.250    -121.88     46.87  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 5 highest-magnitude earthquakes in Q3 2023\n",
    "\n",
    "eq_t.where((eq_t.timestamp >= '2023-06-01') & (eq_t.timestamp < '2023-10-01')) \\\n",
    "  .order_by(eq_t.magnitude, asc=False).limit(5).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that Pixeltable uses Pandas-like operators for filtering data: the expression\n",
    "\n",
    "```python\n",
    "(eq_t.timestamp >= '2023-06-01') & (eq_t.timestamp < '2023-10-01')\n",
    "```\n",
    "\n",
    "means _both_ conditions must be true; similarly (say),\n",
    "\n",
    "```python\n",
    "(eq_t.timestamp < '2023-06-01') | (eq_t.timestamp >= '2024-01-01')\n",
    "```\n",
    "\n",
    "would mean _either_ condition must be true.\n",
    "\n",
    "You can also use the special `isin` operator to select just those values that appear within a particular list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>id</th>\n",
       "      <th>magnitude</th>\n",
       "      <th>location</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>123</td>\n",
       "      <td>1.23</td>\n",
       "      <td>7 km SW of Rainier, Washington</td>\n",
       "      <td>2023-02-17 00:28:25.460</td>\n",
       "      <td>-122.75</td>\n",
       "      <td>46.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>456</td>\n",
       "      <td>0.23</td>\n",
       "      <td>Washington</td>\n",
       "      <td>2023-05-23 08:49:02.450</td>\n",
       "      <td>-121.98</td>\n",
       "      <td>46.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>789</td>\n",
       "      <td>1.67</td>\n",
       "      <td>Puget Sound region, Washington</td>\n",
       "      <td>2023-08-26 04:04:11.200</td>\n",
       "      <td>-122.57</td>\n",
       "      <td>47.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "    id  magnitude                        location               timestamp  \\\n",
       "0  123       1.23  7 km SW of Rainier, Washington 2023-02-17 00:28:25.460   \n",
       "1  456       0.23                      Washington 2023-05-23 08:49:02.450   \n",
       "2  789       1.67  Puget Sound region, Washington 2023-08-26 04:04:11.200   \n",
       "\n",
       "   longitude  latitude  \n",
       "0    -122.75     46.84  \n",
       "1    -121.98     46.87  \n",
       "2    -122.57     47.60  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Earthquakes with specific ids\n",
    "\n",
    "eq_t.where(eq_t.id.isin([123,456,789])).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>-0.83</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "    min  max\n",
       "0 -0.83  4.3"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Min and max magnitudes\n",
    "\n",
    "eq_t.select(min=pxt.functions.min(eq_t.magnitude), max=pxt.functions.max(eq_t.magnitude)).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extracting Data from Tables into Python/Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes it's handy to pull out data from a table into a Python object. We've actually already done this; the call to `collect()` returns an in-memory result set, which we can then dereference in various ways. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 0,\n",
       " 'magnitude': 1.15,\n",
       " 'location': '10 km NW of Belfair, Washington',\n",
       " 'timestamp': datetime.datetime(2023, 1, 1, 8, 10, 37, 50000),\n",
       " 'longitude': -122.93,\n",
       " 'latitude': 47.51}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = eq_t.limit(5).collect()\n",
    "result[0]  # Get the first row of the results as a dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[datetime.datetime(2023, 1, 1, 8, 10, 37, 50000),\n",
       " datetime.datetime(2023, 1, 2, 1, 2, 43, 950000),\n",
       " datetime.datetime(2023, 1, 2, 12, 5, 1, 420000),\n",
       " datetime.datetime(2023, 1, 2, 12, 45, 14, 220000),\n",
       " datetime.datetime(2023, 1, 2, 13, 19, 27, 200000)]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result['timestamp']  # Get a list of the `timestamp` field of all the rows that were queried"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    5.000000\n",
       "mean     0.744000\n",
       "std      0.587988\n",
       "min      0.200000\n",
       "25%      0.290000\n",
       "50%      0.520000\n",
       "75%      1.150000\n",
       "max      1.560000\n",
       "Name: magnitude, dtype: float64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = result.to_pandas()  # Convert the result set into a Pandas dataframe\n",
    "df['magnitude'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`collect()` without a preceding `limit()` returns the entire contents of a query or table. Be careful! For very large tables, this could result in out-of-memory errors. In this example, the 1823 rows in the table fit comfortably into a dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    1823.000000\n",
       "mean        0.900378\n",
       "std         0.625492\n",
       "min        -0.830000\n",
       "25%         0.420000\n",
       "50%         0.850000\n",
       "75%         1.310000\n",
       "max         4.300000\n",
       "Name: magnitude, dtype: float64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = eq_t.collect().to_pandas()\n",
    "df['magnitude'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Like other database tables, Pixeltable tables aren't fixed entities: they're meant to evolve over time. Suppose we want to add a new column to hold user-specified comments about particular earthquake events. We can do this with the `add_column()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 1823 column values with 0 errors.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "UpdateStatus(num_rows=1823, num_computed_values=1823, num_excs=0, updated_cols=[], cols_with_excs=[])"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.add_column(note=pxt.StringType(nullable=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, `note` is the column name, and `pxt.StringType` specifies the type of the new column. `nullable=True` means that it's an _optional_ field. That `nullable=True` is mandatory for newly-added columns, because none of the existing rows have a comment (yet) - so it'd be inconsistent with the existing data to make `note` a required field.\n",
    "\n",
    "An alternate syntax is sometimes convenient for adding columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 1823 column values with 0 errors.\n"
     ]
    }
   ],
   "source": [
    "eq_t['contact_email'] = pxt.StringType(nullable=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's have a look at the revised schema."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_b733c th {\n",
       "  text-align: center;\n",
       "}\n",
       "#T_b733c_row0_col0, #T_b733c_row0_col1, #T_b733c_row0_col2, #T_b733c_row1_col0, #T_b733c_row1_col1, #T_b733c_row1_col2, #T_b733c_row2_col0, #T_b733c_row2_col1, #T_b733c_row2_col2, #T_b733c_row3_col0, #T_b733c_row3_col1, #T_b733c_row3_col2, #T_b733c_row4_col0, #T_b733c_row4_col1, #T_b733c_row4_col2, #T_b733c_row5_col0, #T_b733c_row5_col1, #T_b733c_row5_col2, #T_b733c_row6_col0, #T_b733c_row6_col1, #T_b733c_row6_col2, #T_b733c_row7_col0, #T_b733c_row7_col1, #T_b733c_row7_col2 {\n",
       "  white-space: pre-wrap;\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_b733c\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th id=\"T_b733c_level0_col0\" class=\"col_heading level0 col0\" >Column Name</th>\n",
       "      <th id=\"T_b733c_level0_col1\" class=\"col_heading level0 col1\" >Type</th>\n",
       "      <th id=\"T_b733c_level0_col2\" class=\"col_heading level0 col2\" >Computed With</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_b733c_row0_col0\" class=\"data row0 col0\" >id</td>\n",
       "      <td id=\"T_b733c_row0_col1\" class=\"data row0 col1\" >int</td>\n",
       "      <td id=\"T_b733c_row0_col2\" class=\"data row0 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b733c_row1_col0\" class=\"data row1 col0\" >magnitude</td>\n",
       "      <td id=\"T_b733c_row1_col1\" class=\"data row1 col1\" >float</td>\n",
       "      <td id=\"T_b733c_row1_col2\" class=\"data row1 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b733c_row2_col0\" class=\"data row2 col0\" >location</td>\n",
       "      <td id=\"T_b733c_row2_col1\" class=\"data row2 col1\" >string</td>\n",
       "      <td id=\"T_b733c_row2_col2\" class=\"data row2 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b733c_row3_col0\" class=\"data row3 col0\" >timestamp</td>\n",
       "      <td id=\"T_b733c_row3_col1\" class=\"data row3 col1\" >timestamp</td>\n",
       "      <td id=\"T_b733c_row3_col2\" class=\"data row3 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b733c_row4_col0\" class=\"data row4 col0\" >longitude</td>\n",
       "      <td id=\"T_b733c_row4_col1\" class=\"data row4 col1\" >float</td>\n",
       "      <td id=\"T_b733c_row4_col2\" class=\"data row4 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b733c_row5_col0\" class=\"data row5 col0\" >latitude</td>\n",
       "      <td id=\"T_b733c_row5_col1\" class=\"data row5 col1\" >float</td>\n",
       "      <td id=\"T_b733c_row5_col2\" class=\"data row5 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b733c_row6_col0\" class=\"data row6 col0\" >note</td>\n",
       "      <td id=\"T_b733c_row6_col1\" class=\"data row6 col1\" >string</td>\n",
       "      <td id=\"T_b733c_row6_col2\" class=\"data row6 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b733c_row7_col0\" class=\"data row7 col0\" >contact_email</td>\n",
       "      <td id=\"T_b733c_row7_col1\" class=\"data row7 col1\" >string</td>\n",
       "      <td id=\"T_b733c_row7_col2\" class=\"data row7 col2\" ></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x16fa48a00>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "eq_t.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Updating and Deleting Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Table rows can be modified and deleted with the SQL-like `update()` and `delete()` commands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inserting rows into `earthquakes`: 2 rows [00:00, 734.17 rows/s]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "UpdateStatus(num_rows=2, num_computed_values=0, num_excs=0, updated_cols=['earthquakes.note', 'earthquakes.contact_email'], cols_with_excs=[])"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add a comment to records with IDs 123 and 127\n",
    "\n",
    "eq_t.where(eq_t.id.isin([121,123])).update({'note': 'Still investigating.', 'contact_email': 'contact@pixeltable.com'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>id</th>\n",
       "      <th>magnitude</th>\n",
       "      <th>note</th>\n",
       "      <th>contact_email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>120</td>\n",
       "      <td>1.17</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>121</td>\n",
       "      <td>1.87</td>\n",
       "      <td>Still investigating.</td>\n",
       "      <td>contact@pixeltable.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>122</td>\n",
       "      <td>0.34</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>123</td>\n",
       "      <td>1.23</td>\n",
       "      <td>Still investigating.</td>\n",
       "      <td>contact@pixeltable.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>124</td>\n",
       "      <td>0.13</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "    id  magnitude                  note           contact_email\n",
       "0  120       1.17                  None                    None\n",
       "1  121       1.87  Still investigating.  contact@pixeltable.com\n",
       "2  122       0.34                  None                    None\n",
       "3  123       1.23  Still investigating.  contact@pixeltable.com\n",
       "4  124       0.13                  None                    None"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.where(eq_t.id >= 120).select(eq_t.id, eq_t.magnitude, eq_t.note, eq_t.contact_email).head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another useful method is `batch_update()`, which is used to update many rows at once, in situations where the contents of the update are different for each row. Here's a toy example that shows `batch_update()` in action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UpdateStatus(num_rows=100, num_computed_values=0, num_excs=0, updated_cols=['earthquakes.note'], cols_with_excs=[])"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Update all rows with 500 <= id < 600 with `note` fields such as 'This is note 546'.\n",
    "\n",
    "updates = [\n",
    "    {\n",
    "        'id': n,\n",
    "        'note': f'This is note {n}.'\n",
    "    }\n",
    "    for n in range(500, 600)\n",
    "]\n",
    "eq_t.batch_update(updates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>id</th>\n",
       "      <th>magnitude</th>\n",
       "      <th>note</th>\n",
       "      <th>contact_email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>500</td>\n",
       "      <td>0.75</td>\n",
       "      <td>This is note 500.</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>501</td>\n",
       "      <td>0.23</td>\n",
       "      <td>This is note 501.</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>502</td>\n",
       "      <td>0.43</td>\n",
       "      <td>This is note 502.</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>503</td>\n",
       "      <td>0.31</td>\n",
       "      <td>This is note 503.</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>504</td>\n",
       "      <td>0.35</td>\n",
       "      <td>This is note 504.</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "    id  magnitude               note contact_email\n",
       "0  500       0.75  This is note 500.          None\n",
       "1  501       0.23  This is note 501.          None\n",
       "2  502       0.43  This is note 502.          None\n",
       "3  503       0.31  This is note 503.          None\n",
       "4  504       0.35  This is note 504.          None"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.where(eq_t.id >= 500).select(eq_t.id, eq_t.magnitude, eq_t.note, eq_t.contact_email).head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To delete rows from a table, use the `delete()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UpdateStatus(num_rows=587, num_computed_values=0, num_excs=0, updated_cols=[], cols_with_excs=[])"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Delete all rows in 2024\n",
    "\n",
    "eq_t.where(eq_t.timestamp >= '2024-01-01').delete()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1236"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.count()  # How many are left after deleting?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Don't forget to specify a `where()` clause when using `delete()`! If you run `delete()` without a `where()` clause, the entire contents of the table will be deleted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UpdateStatus(num_rows=1236, num_computed_values=0, num_excs=0, updated_cols=[], cols_with_excs=[])"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.delete()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Table Versioning\n",
    "\n",
    "Every table in Pixeltable is versioned: some or all of its modification history is preserved. If you make a mistake, you can always call `revert()` to undo the previous change to a table. Let's try it out: we'll use it to revert the successive `delete()` calls that we just executed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "eq_t.revert()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1236"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "eq_t.revert()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1823"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "Be aware: calling <code>revert()</code> cannot be undone!\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Multimodal Data\n",
    "\n",
    "In addition to the structured data we've been exploring so far in this tutorial, Pixeltable has native support for __media types__: images, video, audio, and unstructured documents such as pdfs. Media support is one of Pixeltable's core capabilities, and we'll have much more to say about it the upcoming [Unstructured Data](...) section of this tutorial. For now, we'll just give one example to show how media data lives side-by-side with structured data in Pixeltable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 1823 column values with 0 errors.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_963db th {\n",
       "  text-align: center;\n",
       "}\n",
       "#T_963db_row0_col0, #T_963db_row0_col1, #T_963db_row0_col2, #T_963db_row1_col0, #T_963db_row1_col1, #T_963db_row1_col2, #T_963db_row2_col0, #T_963db_row2_col1, #T_963db_row2_col2, #T_963db_row3_col0, #T_963db_row3_col1, #T_963db_row3_col2, #T_963db_row4_col0, #T_963db_row4_col1, #T_963db_row4_col2, #T_963db_row5_col0, #T_963db_row5_col1, #T_963db_row5_col2, #T_963db_row6_col0, #T_963db_row6_col1, #T_963db_row6_col2, #T_963db_row7_col0, #T_963db_row7_col1, #T_963db_row7_col2, #T_963db_row8_col0, #T_963db_row8_col1, #T_963db_row8_col2 {\n",
       "  white-space: pre-wrap;\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_963db\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th id=\"T_963db_level0_col0\" class=\"col_heading level0 col0\" >Column Name</th>\n",
       "      <th id=\"T_963db_level0_col1\" class=\"col_heading level0 col1\" >Type</th>\n",
       "      <th id=\"T_963db_level0_col2\" class=\"col_heading level0 col2\" >Computed With</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_963db_row0_col0\" class=\"data row0 col0\" >id</td>\n",
       "      <td id=\"T_963db_row0_col1\" class=\"data row0 col1\" >int</td>\n",
       "      <td id=\"T_963db_row0_col2\" class=\"data row0 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_963db_row1_col0\" class=\"data row1 col0\" >magnitude</td>\n",
       "      <td id=\"T_963db_row1_col1\" class=\"data row1 col1\" >float</td>\n",
       "      <td id=\"T_963db_row1_col2\" class=\"data row1 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_963db_row2_col0\" class=\"data row2 col0\" >location</td>\n",
       "      <td id=\"T_963db_row2_col1\" class=\"data row2 col1\" >string</td>\n",
       "      <td id=\"T_963db_row2_col2\" class=\"data row2 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_963db_row3_col0\" class=\"data row3 col0\" >timestamp</td>\n",
       "      <td id=\"T_963db_row3_col1\" class=\"data row3 col1\" >timestamp</td>\n",
       "      <td id=\"T_963db_row3_col2\" class=\"data row3 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_963db_row4_col0\" class=\"data row4 col0\" >longitude</td>\n",
       "      <td id=\"T_963db_row4_col1\" class=\"data row4 col1\" >float</td>\n",
       "      <td id=\"T_963db_row4_col2\" class=\"data row4 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_963db_row5_col0\" class=\"data row5 col0\" >latitude</td>\n",
       "      <td id=\"T_963db_row5_col1\" class=\"data row5 col1\" >float</td>\n",
       "      <td id=\"T_963db_row5_col2\" class=\"data row5 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_963db_row6_col0\" class=\"data row6 col0\" >note</td>\n",
       "      <td id=\"T_963db_row6_col1\" class=\"data row6 col1\" >string</td>\n",
       "      <td id=\"T_963db_row6_col2\" class=\"data row6 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_963db_row7_col0\" class=\"data row7 col0\" >contact_email</td>\n",
       "      <td id=\"T_963db_row7_col1\" class=\"data row7 col1\" >string</td>\n",
       "      <td id=\"T_963db_row7_col2\" class=\"data row7 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_963db_row8_col0\" class=\"data row8 col0\" >map_image</td>\n",
       "      <td id=\"T_963db_row8_col1\" class=\"data row8 col1\" >image</td>\n",
       "      <td id=\"T_963db_row8_col2\" class=\"data row8 col2\" ></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x16f9fa9d0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "eq_t['map_image'] = pxt.ImageType(nullable=True)  # Add a new `ImageType` column\n",
    "eq_t.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inserting rows into `earthquakes`: 1 rows [00:00, 1438.87 rows/s]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "UpdateStatus(num_rows=1, num_computed_values=0, num_excs=0, updated_cols=['earthquakes.map_image'], cols_with_excs=[])"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Update the row with id == 1002, adding an image to the `map_image` column\n",
    "\n",
    "eq_t.where(eq_t.id == 1002).update(\n",
    "    {'map_image': 'https://raw.githubusercontent.com/pixeltable/pixeltable/master/docs/source/data/port-townsend-map.jpeg'}\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just like when using `import_csv`, you can refer to images with either a local file path or a URL. Pixeltable will embed image thumbnails in your notebook when you do a query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>id</th>\n",
       "      <th>magnitude</th>\n",
       "      <th>location</th>\n",
       "      <th>map_image</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1000</td>\n",
       "      <td>-0.02</td>\n",
       "      <td>17 km SSE of Carbonado, Washington</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1001</td>\n",
       "      <td>0.82</td>\n",
       "      <td>22 km ENE of Ashford, Washington</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1002</td>\n",
       "      <td>4.3</td>\n",
       "      <td>Port Townsend, WA</td>\n",
       "      <td><div class=\"pxt_image\" style=\"width:240px;\">\n",
       "                <img src=\"\" width=\"240\" />\n",
       "            </div></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1003</td>\n",
       "      <td>1.04</td>\n",
       "      <td>Washington</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1004</td>\n",
       "      <td>0.79</td>\n",
       "      <td>24 km ENE of Ashford, Washington</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "     id  magnitude                            location  \\\n",
       "0  1000      -0.02  17 km SSE of Carbonado, Washington   \n",
       "1  1001       0.82    22 km ENE of Ashford, Washington   \n",
       "2  1002       4.30                   Port Townsend, WA   \n",
       "3  1003       1.04                          Washington   \n",
       "4  1004       0.79    24 km ENE of Ashford, Washington   \n",
       "\n",
       "                                           map_image  \n",
       "0                                               None  \n",
       "1                                               None  \n",
       "2  <PIL.JpegImagePlugin.JpegImageFile image mode=...  \n",
       "3                                               None  \n",
       "4                                               None  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_t.where(eq_t.id >= 1000).select(eq_t.id, eq_t.magnitude, eq_t.location, eq_t.map_image).head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Directory Hierarchies\n",
    "\n",
    "So far we've only seen an example of a single directory with a table inside it, but one can also put directories inside other directories, in whatever fashion makes the most sense for a given application."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Created directory `fundamentals.subdir`.\n",
      "Created directory `fundamentals.subdir.subsubdir`.\n",
      "Created table `my_table`.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_0b2f1 th {\n",
       "  text-align: center;\n",
       "}\n",
       "#T_0b2f1_row0_col0, #T_0b2f1_row0_col1, #T_0b2f1_row0_col2 {\n",
       "  white-space: pre-wrap;\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_0b2f1\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th id=\"T_0b2f1_level0_col0\" class=\"col_heading level0 col0\" >Column Name</th>\n",
       "      <th id=\"T_0b2f1_level0_col1\" class=\"col_heading level0 col1\" >Type</th>\n",
       "      <th id=\"T_0b2f1_level0_col2\" class=\"col_heading level0 col2\" >Computed With</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_0b2f1_row0_col0\" class=\"data row0 col0\" >my_col</td>\n",
       "      <td id=\"T_0b2f1_row0_col1\" class=\"data row0 col1\" >string</td>\n",
       "      <td id=\"T_0b2f1_row0_col2\" class=\"data row0 col2\" ></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "table 'my_table'\n",
       "\n",
       "Column Name   Type Computed With\n",
       "     my_col string              "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pxt.create_dir('fundamentals.subdir')\n",
    "pxt.create_dir('fundamentals.subdir.subsubdir')\n",
    "pxt.create_table('fundamentals.subdir.subsubdir.my_table', {'my_col': pxt.StringType()})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deleting Columns, Tables, and Directories"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`drop_column()`, `drop_table()`, and `drop_dir()` are used to delete columns, tables, and directories, respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete the `contact_email` column\n",
    "\n",
    "eq_t.drop_column('contact_email')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_c9cca th {\n",
       "  text-align: center;\n",
       "}\n",
       "#T_c9cca_row0_col0, #T_c9cca_row0_col1, #T_c9cca_row0_col2, #T_c9cca_row1_col0, #T_c9cca_row1_col1, #T_c9cca_row1_col2, #T_c9cca_row2_col0, #T_c9cca_row2_col1, #T_c9cca_row2_col2, #T_c9cca_row3_col0, #T_c9cca_row3_col1, #T_c9cca_row3_col2, #T_c9cca_row4_col0, #T_c9cca_row4_col1, #T_c9cca_row4_col2, #T_c9cca_row5_col0, #T_c9cca_row5_col1, #T_c9cca_row5_col2, #T_c9cca_row6_col0, #T_c9cca_row6_col1, #T_c9cca_row6_col2, #T_c9cca_row7_col0, #T_c9cca_row7_col1, #T_c9cca_row7_col2 {\n",
       "  white-space: pre-wrap;\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_c9cca\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th id=\"T_c9cca_level0_col0\" class=\"col_heading level0 col0\" >Column Name</th>\n",
       "      <th id=\"T_c9cca_level0_col1\" class=\"col_heading level0 col1\" >Type</th>\n",
       "      <th id=\"T_c9cca_level0_col2\" class=\"col_heading level0 col2\" >Computed With</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_c9cca_row0_col0\" class=\"data row0 col0\" >id</td>\n",
       "      <td id=\"T_c9cca_row0_col1\" class=\"data row0 col1\" >int</td>\n",
       "      <td id=\"T_c9cca_row0_col2\" class=\"data row0 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c9cca_row1_col0\" class=\"data row1 col0\" >magnitude</td>\n",
       "      <td id=\"T_c9cca_row1_col1\" class=\"data row1 col1\" >float</td>\n",
       "      <td id=\"T_c9cca_row1_col2\" class=\"data row1 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c9cca_row2_col0\" class=\"data row2 col0\" >location</td>\n",
       "      <td id=\"T_c9cca_row2_col1\" class=\"data row2 col1\" >string</td>\n",
       "      <td id=\"T_c9cca_row2_col2\" class=\"data row2 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c9cca_row3_col0\" class=\"data row3 col0\" >timestamp</td>\n",
       "      <td id=\"T_c9cca_row3_col1\" class=\"data row3 col1\" >timestamp</td>\n",
       "      <td id=\"T_c9cca_row3_col2\" class=\"data row3 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c9cca_row4_col0\" class=\"data row4 col0\" >longitude</td>\n",
       "      <td id=\"T_c9cca_row4_col1\" class=\"data row4 col1\" >float</td>\n",
       "      <td id=\"T_c9cca_row4_col2\" class=\"data row4 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c9cca_row5_col0\" class=\"data row5 col0\" >latitude</td>\n",
       "      <td id=\"T_c9cca_row5_col1\" class=\"data row5 col1\" >float</td>\n",
       "      <td id=\"T_c9cca_row5_col2\" class=\"data row5 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c9cca_row6_col0\" class=\"data row6 col0\" >note</td>\n",
       "      <td id=\"T_c9cca_row6_col1\" class=\"data row6 col1\" >string</td>\n",
       "      <td id=\"T_c9cca_row6_col2\" class=\"data row6 col2\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c9cca_row7_col0\" class=\"data row7 col0\" >map_image</td>\n",
       "      <td id=\"T_c9cca_row7_col1\" class=\"data row7 col1\" >image</td>\n",
       "      <td id=\"T_c9cca_row7_col2\" class=\"data row7 col2\" ></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x340690820>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "eq_t.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete the entire table (cannot be reverted!)\n",
    "\n",
    "pxt.drop_table('fundamentals.earthquakes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete the entire directory and all its contents, including any nested\n",
    "# subdirectories (cannot be reverted!)\n",
    "\n",
    "pxt.drop_dir('fundamentals', force=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's the end of this section of the Fundamentals tutorial! Continue on to the next section:\n",
    "- [Computed Columns](...)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
